/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package cafe.Model.Utility;

import com.mysql.jdbc.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
 *
 * @author Deka
 */
public class DBConnection {

    private DatabaseInfo databaseInfo;

    private Connection connection; //Database connection,     
    private PreparedStatement statement; //statement 
    private boolean isBusy;

    public DBConnection(String driver, String dbms, String host, String port, String username, String password, String database) {
        this.databaseInfo = new DatabaseInfo(driver, dbms, host, port,
                database, username, password);

        // create connection and statement with default value
        try {
            Class.forName(this.databaseInfo.getDBMSDriver());
            this.connect();

            //statement = connection.prepareStatement();
        } catch (SQLException ex) {
            Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
        } 

        //connection is free now
        this.isBusy = true;

    }

    public DBConnection(DatabaseInfo databaseInfo) {
        this.databaseInfo = databaseInfo;

        // create connection and statement with default value
        try {
            //Class.forName(this.databaseInfo.getDBMSDriver());
            DriverManager.registerDriver(new Driver());
            this.connect();

            //statement = connection.prepareStatement();
        } catch (  SQLException ex) {
            Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
        }  

        //connection is free now
        this.isBusy = true;
    }

    private void setPatameters(Object... parameters) throws SQLException {
        //set parameters
        for (int i = 0; i < parameters.length; i++) {
            ((PreparedStatement) this.getStatement()).setObject(i + 1, parameters[i]);
        }
    }

    private void connect() throws SQLException {
        //reconnecto to server using a new connection
        this.setConnection(DriverManager.getConnection(
                this.getDatabaseInfo().getURL(),
                this.getDatabaseInfo().getUsername(), this.getDatabaseInfo().getPassword()));

        //this.statement = connection.createStatement();
    }

    public void prepare(String preparedQuery) throws SQLException {
        if (null != this.getStatement()) {
            this.getStatement().close();
        }
        this.setStatement(this.getConnection().prepareStatement(preparedQuery));
    }

    public void prepare(String preparedQuery, int isGeneratedKeyS) throws SQLException {
         if (null != this.getStatement()) {
            this.getStatement().close();
        }
        this.setStatement(this.getConnection().prepareStatement(preparedQuery, isGeneratedKeyS));
    }
    
    /**
     * Note: have prepared statement before
     *
     * @param parameters
     * @return
     * @throws SQLException
     */
    public ResultSet query(Object... parameters) throws SQLException {
        //set parameters
        this.getStatement().clearParameters();
        this.setPatameters(parameters);

        //query
        return ((PreparedStatement) this.getStatement()).executeQuery();
    }

    /**
     * prepare statement and query
     *
     * @param preparedQuery
     * @param parameters
     * @return
     * @throws SQLException
     */
    public ResultSet query(String preparedQuery, Object... parameters) throws SQLException {
        //prepare statement
        this.prepare(preparedQuery);

        //set parameters & query
        return this.query(parameters);
    }

    public ResultSet query(String preparedQuery) throws SQLException {
        //prepare statement
        this.prepare(preparedQuery);

        //set parameters & query
        return this.query();
    }

    /**
     * Note: have prepared statement before
     *
     * @param parameters
     * @return
     * @throws SQLException
     */
    public int excute(Object... parameters) throws SQLException {
        //set parameters
        this.statement.clearParameters();
        this.setPatameters(parameters);

        //excute
        return ((PreparedStatement) this.getStatement()).executeUpdate();
    }

    public int excute(String preparedQuery, Object... parameters) throws SQLException {
        //prepare statement
       
        this.prepare(preparedQuery);
        
        //excute
        return this.excute(parameters);
    }

    public ResultSet excuteGeneratedKey(String preparedQuery, Object... parameters) throws SQLException{
        //prepare statement       
        this.prepare(preparedQuery, Statement.RETURN_GENERATED_KEYS);
           
        //set parameters
        this.statement.clearParameters();
        this.setPatameters(parameters);

        //excute
        this.getStatement().executeUpdate();
        return ((PreparedStatement) this.getStatement()).getGeneratedKeys();
    }
    
    public DBConnection reconnect() {
        try {
            //check if the connection is closed because of timeout
            if (this.getConnection().isClosed()) {
                //reconnecto to server using a new connection
                this.connect();
            }

        } catch (SQLException ex) {

        }

        return this;
    }

    /**
     * Close connection
     */
    public void close() {
        try {
            if (getStatement() != null) {
                getStatement().close();
            }

            if (getConnection() != null) {
                getConnection().close();
            }
        } catch (Exception e) {
        }
    }

    // <editor-fold defaultstate="collapsed" desc="Get-Set Method">
    /**
     * @return the databaseInfo
     */
    public DatabaseInfo getDatabaseInfo() {
        return databaseInfo;
    }

    /**
     * @param databaseInfo the databaseInfo to set
     */
    public void setDatabaseInfo(DatabaseInfo databaseInfo) {
        this.databaseInfo = databaseInfo;
    }

    /**
     * @return the connection
     */
    public Connection getConnection() {
        return connection;
    }

    /**
     * @param connection the connection to set
     */
    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    /**
     * @return the statement
     */
    public PreparedStatement getStatement() {
        return statement;
    }

    /**
     * @param statement the statement to set
     */
    public void setStatement(PreparedStatement statement) {
        this.statement = statement;
    }

    /**
     * @return the isBusy
     */
    public boolean isIsBusy() {
        return isBusy;
    }

    /**
     * @param isBusy the isBusy to set
     */
    public void setIsBusy(boolean isBusy) {
        this.isBusy = isBusy;
    }

    // </editor-fold>

    
}
